package com.matt.foundation.sqlOperator;


import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.utils.ModelUtil;

import java.sql.*;
import java.util.Date;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/7
 * *******************************
 */
public class TeachingEarlyTransfer {

//    public static final String localUrl = "jdbc:mysql://localhost:3306/vega_dev";
//    public static final String localName = "root";
//    public static final String localPWd = "123456";

    public static final String localUrl = "jdbc:mysql://rm-bp1xxa9jd5ajt2km8o.mysql.rds.aliyuncs.com:3306/vega_dev";
    public static final String localName = "matt";
    public static final String localPWd = "HgY5CcO4";

//    public static final String localUrl = "jdbc:mysql://rm-bp151a4j302p495nso.mysql.rds.aliyuncs.com:3306/vega_prd";
//    public static final String localName = "matt";
//    public static final String localPWd = "HgY5CcO4";

    public static void main(String[] args) throws Exception{
//        transferType();
//        transferMetail();
        processOldData();
    }

    public static void transferType() {
        int num = 0;
        String remoteUrl = "jdbc:mysql://localhost:3306/matt_teach_system";
        String remoteName = "root";
        String remotePWd = "123456";

        String localUrl = "jdbc:mysql://localhost:3306/vega";
        String localName = "root";
        String localPWd = "123456";
        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select * from tblsubject  where fldCode not in(10, 11, 12, 13,14) ORDER BY fldCode ";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;
            while (remoteResultSet.next()){
                String code = remoteResultSet.getString(1); //value
                String name = remoteResultSet.getString(2); //题目
                Date created = remoteResultSet.getDate(16); //

//                Date createDate = remoteResultSet.getObject(6) == null? null : ( (Date) remoteResultSet.getObject(6)); //传教时间
//                Integer type = remoteResultSet.getInt(7); //4菜谱
//                if(type == 1){
//                    //行为
//                }else if(type == 2){
//                    //游戏
//                }else if(type ==3){
//                    //儿保
//                }else if(type == 100){
//                    //月子餐
//                }

                //8跳过，ApiGroupID
                //9跳过，Tools
//                String img = rs.getString(10);//图片

//                Integer month = rs.getInt(16);//月份
                System.out.println("value :" + code);
                System.out.println("name :" +name);
                System.out.println("created :" +created);
//                System.out.println("img :" + img);
//                System.out.println("month :" + month);


                String insert = "insert into teaching_type_early (id, name, deleted, created) VALUEs (?, ?, ?, ?) ";
//
                localPreparedStatement = localConn.prepareStatement(insert);
//
                localPreparedStatement.setString(1, code);
                localPreparedStatement.setString(2, name);
                localPreparedStatement.setInt(3, 0);
                localPreparedStatement.setDate(4, new java.sql.Date(created.getTime()));
//                localPreparedStatement.setDate(6, new java.sql.Date(createDate.getTime()));
                localPreparedStatement.execute();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void transferMetail() {
        int num = 0;
        String remoteUrl = "jdbc:mysql://localhost:3306/matt_teach_system";
        String remoteName = "root";
        String remotePWd = "123456";

        String localUrl = "jdbc:mysql://localhost:3306/vega";
        String localName = "root";
        String localPWd = "123456";

        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select d.fldSubjectCode, d.fldWeek, d.fldContent,d.fldCreateDate from tblSubjectDetail d where d.fldSubjectCode not in(10, 11, 12, 13,14) order by d.fldSubjectCode, d.fldWeek ";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;

            Integer count =0;
            while (remoteResultSet.next()){
                Integer code = remoteResultSet.getInt(1); //id
                Integer week = remoteResultSet.getInt(2); //月份
                String content = remoteResultSet.getString(3); //天数
                java.sql.Date created =  remoteResultSet.getDate(4); //对应资料id

//                System.out.println("value :" + value);
//                System.out.println("week :" + week);
//                System.out.println("content :" +content);
//                System.out.println("created :" + created);
//                if (content.contains("见附录Ⅲ：")){
////                    System.out.println(content);
//                    content = content.substring(content.indexOf("见附录Ⅲ："), content.indexOf("见附录Ⅲ：") + 20);
//                    System.out.println(content);
//
//                }

                String insert = "insert into teaching_material_early(id, week, content, filePath, typeId, remark, created, deleted) values( ?, ?, ?, ?, ?, ?, ?, ?)";
//
                localPreparedStatement = localConn.prepareStatement(insert);
//
                localPreparedStatement.setString(1, null);
                localPreparedStatement.setInt(2, week);
                localPreparedStatement.setString(3, content);
                localPreparedStatement.setString(4, "");
                localPreparedStatement.setLong(5, code);
                localPreparedStatement.setString(6, "");
                localPreparedStatement.setDate(7, created);
                localPreparedStatement.setBoolean(8, false);
                localPreparedStatement.execute();
            }
            System.out.println(count);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void processOldData(){

         try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection remoteConn = DriverManager.getConnection(localUrl, localName, localPWd);
            String query = "select id  from course_official_old ";
            PreparedStatement remotePreparedStatement = remoteConn.prepareStatement(query);
            ResultSet remoteResultSet = remotePreparedStatement.executeQuery();

            while (remoteResultSet.next()){
                long id = remoteResultSet.getLong(1); //aid
                String update = "update course_official_old set uid=? where id=? ";
                String uid = ModelUtil.getCleanUUID();
                remotePreparedStatement = remoteConn.prepareStatement(update);
                System.out.println("id :" + id);
                System.out.println("uid :" + uid);
                remotePreparedStatement.setString(1, uid);
                remotePreparedStatement.setLong(2, id);
                remotePreparedStatement.execute();

            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

}
